package edu.wfmanage.models;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.util.DB;
/**
Author: Satya Deepthi Bhagi
**/

public class Claim extends User{

private String submittedby;
private int RequestId;
private String mode;
private String TicketNor;
private int Distance;
private Date travelDate;
private String Source;
private String Destination;
private int Fare;
private int Limit;
private int approvedAmt=0;
private String Status;
private Timestamp ReqSubmissionTime;
private Timestamp verificationDate;
private Timestamp financeAprrovalDate;
private String verificationStatus;
private String financeStatus;
private String verifiedBy;
private String financeApprovalBy;
private int ApprovedAmount;

public int insert() {
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");  
    System.out.println("New Date--->" + format.format(travelDate));
    	
	System.out.println("getUserid()"+getUserid());
	String insertSQL = "insert into REQUEST "
			+ "(USER_ID, MODE_OF_TRAVEL, TICKET_NUMBER, TRAVELLED_FROM, DESTINATION, DISTANCE_KMS,TRAVEL_DATE,"
			+"TRAVELLING_FARE" 
			+") " + "values('" + getUserid() + "','" + mode + "', '" + TicketNor + "', '" + Source + "', '" + Destination + "', " + Distance+",'" + format.format(travelDate) + "', " + Fare+")";

	return DB.update(insertSQL);
}

public static int updatedb(String updatesql) {
	
	return DB.update(updatesql);
}

public static ArrayList<Claim> find(String selectionModifier) {
	ArrayList<Claim> selection = new ArrayList<Claim>();
	ResultSet resultSet = null;
	String query = "select * from request " + selectionModifier ;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(query, connection);
	try {
		while (resultSet.next()) {
			Claim claim = new Claim();
			claim.RequestId = resultSet.getInt("REQ_ID");
			claim.mode = resultSet.getString("MODE_OF_TRAVEL");
			claim.Distance = resultSet.getInt("DISTANCE_KMS");
			claim.Source = resultSet.getString("TRAVELLED_FROM");
			claim.Destination = resultSet.getString("DESTINATION");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
			claim.travelDate = resultSet.getDate("TRAVEL_DATE");
			claim.approvedAmt = resultSet.getInt("APPROVED_AMT");
			claim.Status=resultSet.getString("Status");
			selection.add(claim);
	
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return selection;
}

public static ArrayList<Claim> findverifyitems(String selectionModifier) {
	ArrayList<Claim> selection = new ArrayList<Claim>();
	ResultSet resultSet = null;
	String query = "select * from request " + selectionModifier ;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(query, connection);
	try {
		while (resultSet.next()) {
			Claim claim = new Claim();
			claim.RequestId = resultSet.getInt("REQ_ID");
			claim.submittedby=resultSet.getString("USER_ID");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
			claim.verificationStatus = resultSet.getString("VERIFY_DPT_STATUS");
			selection.add(claim);	
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return selection;
}

public static ArrayList<Claim> findfinanceitems(String sql,int limit) {
	ArrayList<Claim> selection = new ArrayList<Claim>();
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			Claim claim = new Claim();
			claim.RequestId = resultSet.getInt("REQ_ID");
			claim.submittedby=resultSet.getString("USER_ID");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
			claim.financeStatus = resultSet.getString("FINANCE_DPT_STATUS");
			if(claim.Fare<limit){
				claim.ApprovedAmount=claim.Fare;
			}
			if(claim.Fare>=limit){
				claim.ApprovedAmount=limit;			
			}
			selection.add(claim);	
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return selection;
}

public static int getLTALimit(String sql) {
	int LTALimit=0;
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			LTALimit=resultSet.getInt("LTA_Limit");	
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return LTALimit;
}


public static String getUserId(String sql) {
	String Uid = null;
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			Uid=resultSet.getString("USER_ID");	
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return Uid;
}
public static ArrayList<Claim> getHistory(String sql) {
	ArrayList<Claim> selection = new ArrayList<Claim>();
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			Claim claim = new Claim();
			claim.RequestId = resultSet.getInt("REQ_ID");
			claim.submittedby=resultSet.getString("USER_ID");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
			claim.verificationStatus = resultSet.getString("VERIFY_DPT_STATUS");
			claim.financeStatus=resultSet.getString("FINANCE_DPT_STATUS");
			claim.ApprovedAmount=resultSet.getInt("APPROVED_AMT");
			selection.add(claim);
			System.out.println(claim.RequestId);
		}
		System.out.println("Array size is:"+ selection.size());
		
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	DB.close(connection);
	return selection;
}


public  static Claim findone(String sql){
	Claim claim=new Claim();
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			claim.RequestId=resultSet.getInt("REQ_ID");
			claim.ReqSubmissionTime=resultSet.getTimestamp("REQ_SUBMISSION_TIME");
			claim.verificationStatus=resultSet.getString("VERIFY_DPT_STATUS");
			claim.verifiedBy=resultSet.getString("VERIFIED_BY");
			claim.verificationDate=resultSet.getTimestamp("VERIFY_DPT_ACTION_TIME");
			claim.financeApprovalBy=resultSet.getString("FINANCE_APPROVAL_BY");
			claim.financeAprrovalDate=resultSet.getTimestamp("FINANCE_DPT_ACTION_TIME");
			claim.financeStatus=resultSet.getString("FINANCE_DPT_STATUS");
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	return claim;
}

public  static Claim findonetoverify(String sql){
	Claim claim=new Claim();
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			claim.RequestId=resultSet.getInt("REQ_ID");
			claim.mode = resultSet.getString("MODE_OF_TRAVEL");
			claim.Distance = resultSet.getInt("DISTANCE_KMS");
			claim.Source = resultSet.getString("TRAVELLED_FROM");
			claim.Destination = resultSet.getString("DESTINATION");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	return claim;
}

public  static Claim findonetoapprove(String sql){
	Claim claim=new Claim();
	ResultSet resultSet = null;
	Connection connection = DB.getConnection();
	resultSet = DB.readFromDB(sql, connection);
	try {
		while (resultSet.next()) {
			claim.RequestId=resultSet.getInt("REQ_ID");
			claim.mode = resultSet.getString("MODE_OF_TRAVEL");
			claim.Distance = resultSet.getInt("DISTANCE_KMS");
			claim.Source = resultSet.getString("TRAVELLED_FROM");
			claim.Destination = resultSet.getString("DESTINATION");
			claim.Fare = resultSet.getInt("TRAVELLING_FARE");
		}
	} catch (SQLException e) {
       System.out.println("Exception while reading from db"+ e);
	}
	return claim;
}

public int getApprovedAmount() {
	return ApprovedAmount;
}

public void setApprovedAmount(int approvedAmount) {
	ApprovedAmount = approvedAmount;
}

public String getSubmittedby() {
	return submittedby;
}

public void setSubmittedby(String submittedby) {
	this.submittedby = submittedby;
}

public String getVerifiedBy() {
	return verifiedBy;
}

public void setVerifiedBy(String verifiedBy) {
	this.verifiedBy = verifiedBy;
}

public String getFinanceApprovalBy() {
	return financeApprovalBy;
}

public void setFinanceApprovalBy(String financeApprovalBy) {
	this.financeApprovalBy = financeApprovalBy;
}

public Timestamp getReqSubmissionTime() {
	return ReqSubmissionTime;
}

public void setReqSubmissionTime(Timestamp reqSubmissionTime) {
	ReqSubmissionTime = reqSubmissionTime;
}

public Timestamp getVerificationDate() {
	return verificationDate;
}

public void setVerificationDate(Timestamp verificationDate) {
	this.verificationDate = verificationDate;
}

public Timestamp getFinanceAprrovalDate() {
	return financeAprrovalDate;
}

public void setFinanceAprrovalDate(Timestamp financeAprrovalDate) {
	this.financeAprrovalDate = financeAprrovalDate;
}

public String getVerificationStatus() {
	return verificationStatus;
}

public void setVerificationStatus(String verificationStatus) {
	this.verificationStatus = verificationStatus;
}

public String getFinanceStatus() {
	return financeStatus;
}

public void setFinanceStatus(String financeStatus) {
	this.financeStatus = financeStatus;
}

public String getStatus() {
	return Status;
}

public void setStatus(String status) {
	Status = status;
}

public int getApprovedAmt() {
	return approvedAmt;
}

public void setApprovedAmt(int approvedAmt) {
	this.approvedAmt = approvedAmt;
}

public int getRequestId() {
	return RequestId;
}

public void setRequestId(int requestId) {
	RequestId = requestId;
}

public String getMode() {
	return mode;
}
public void setMode(String mode) {
	this.mode = mode;
}
public String getTicketNor() {
	return TicketNor;
}
public void setTicketNor(String ticketNor) {
	TicketNor = ticketNor;
}

public int getDistance() {
	return Distance;
}
public void setDistance(int distance) {
	Distance = distance;
}

public Date getTravelDate() {
	return travelDate;
}

public void setTravelDate(Date travelDate) {
	this.travelDate = travelDate;
}

public String getSource() {
	return Source;
}
public void setSource(String source) {
	Source = source;
}
public String getDestination() {
	return Destination;
}
public void setDestination(String destination) {
	Destination = destination;
}
public int getFare() {
	return Fare;
}
public void setFare(int fare) {
	Fare = fare;
}
public int getLimit() {
	return Limit;
}
public void setLimit(int limit) {
	Limit = limit;
}

}
